package dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import pojo.Customer;
import util.DBUtil;
import dao.CustomerDao;

public class CustomerDaoImpl implements CustomerDao{

	public int addCustomer(Customer customer) {
		String sql="insert into customers(id,cname,sex,sal,birth) values (seq01.nextval,?,?,?,?)";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, customer.getCname());
			pst.setBoolean(2, customer.isSex());
			pst.setDouble(3, customer.getSal());
			pst.setDate(4, new java.sql.Date(customer.getBirth().getTime()));
			m=pst.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil.close(null, pst, null);
		}
	}

	public int deleteCustomer(Integer id) {
		String sql="delete from customers where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			m=pst.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil.close(null, pst, null);
		}
	}

	public int updateCustomer(Customer customer) {
		String sql="update customers set cname=?,sex=?,sal=?,birth=? where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		int m=0;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setString(1, customer.getCname());
			pst.setBoolean(2, customer.isSex());
			pst.setDouble(3, customer.getSal());
			pst.setDate(4, new java.sql.Date(customer.getBirth().getTime()));
			pst.setInt(5, customer.getId());
			m=pst.executeUpdate();
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		} finally{
			DBUtil.close(null, pst, null);
		}
	}

	public Customer loadCustomer(Integer id) {
		String sql="select * from customers where id=?";
		Connection connection = null;
		PreparedStatement pst=null;
		Customer customer = null;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			pst.setInt(1, id);
			ResultSet rs = null;
			rs=pst.executeQuery();
			while(rs.next()){
				customer = new Customer();
				customer.setId(rs.getInt("id"));
				customer.setCname(rs.getString("cname"));
				customer.setSex(rs.getBoolean("sex"));
				customer.setSal(rs.getDouble("sal"));
				customer.setBirth(rs.getDate("birth"));
			}
			return customer;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}

	public List<Customer> findAll() {
		List<Customer> list = new ArrayList<Customer>();
		
		String sql="select * from customers";
		Connection connection = null;
		PreparedStatement pst=null;
		try {
			connection = DBUtil.createConnection();
			pst = connection.prepareStatement(sql);
			ResultSet rs = null;
			rs=pst.executeQuery();
			
			while(rs.next()){
				Customer customer = new Customer();
				customer.setId(rs.getInt("id"));
				customer.setCname(rs.getString("cname"));
				customer.setSex(rs.getBoolean("sex"));
				customer.setSal(rs.getDouble("sal"));
				customer.setBirth(rs.getDate("birth"));
				list.add(customer);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return list;
		} finally{
			DBUtil.close(connection, pst, null);
		}
	}
}
